package org.power.bus.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.text.csv.CsvRow;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.apache.commons.io.FilenameUtils;
import org.apache.commons.lang3.StringUtils;
import org.power.bus.dao.*;
import org.power.bus.entity.CountResponse;
import org.power.bus.entity.TrainParam;
import org.power.bus.model.*;
import org.power.core.common.CommonPage;
import org.power.core.common.PageParamRequest;
import org.power.core.config.PowerConfig;
import org.power.core.exception.PowerException;
import org.power.core.util.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import org.power.core.consts.Constants;
import org.power.core.beans.UploadCommonVo;
import org.power.bus.service.PowerService;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import java.io.File;
import java.util.*;
import java.util.concurrent.*;

@Service
public class PowerServiceImpl implements PowerService {
    private static final Logger LOGGER = LoggerFactory.getLogger(PowerServiceImpl.class);

    private static final String OS = System.getProperty("os.name").toLowerCase();

    @Resource
    private PowerConfig powerConfig;

    @Value("${power.model_file_path}")
    private static String modelFilePath = "/data/power/model";

    @Value("${power.python}")
    private static String python = "python";

    @Resource
    private EquipmentDao equipmentDao;

    @Resource
    private TemperatureDao temperatureDao;

    @Resource
    private CurrentDao currentDao;

    @Resource
    private UploadDao uploadDao;

    @Resource
    private FeatureDao featureDao;

    @Resource
    private PredictFeatureDao predictFeatureDao;

    private ExecutorService executorService = Executors.newSingleThreadExecutor();

    private static final Map<String, Integer> equipmentMap = new HashMap<>();

    // 后期建议从数据库或redis中获取
    private static final Map<String, String> AVAILABLE_MODELS_MAP = new HashMap<>();

    private List<Map<String, String>> deviceNumberlList = new ArrayList<>();

    private List<Map<String, String>> deviceNamelList = new ArrayList<>();


    /**
     * 电流表名称到设备名称的映射
     * TODO升高座
     * 每个电流表对应的是A、B、C三相设备吗？
     */
    private static final Map<String, String> currentMap = new HashMap<>();

    static {

//        currentMap.put("#1主变35kV", "1号主变35KV套管接线夹");
//        currentMap.put("#1主变220kV", "1号主变220KV套管接线夹");
//        currentMap.put("#1主变500kV", "1号主变500KV套管接线夹");

        currentMap.put("#2主变35kV", "2号主变35KV套管接线夹");
//        currentMap.put("#2主变220kV", "2号主变220KV套管接线夹");
//        currentMap.put("#2主变500kV", "2号主变500KV套管接线夹");

//        currentMap.put("#1主变35kV", "1号主变35KV套管升高座");
//        currentMap.put("#1主变220kV", "1号主变220KV套管升高座");
//        currentMap.put("#1主变500kV", "1号主变500KV套管升高座");
//
//        currentMap.put("#2主变35kV", "2号主变35KV套管升高座");
//        currentMap.put("#2主变220kV", "2号主变220KV套管升高座");
//        currentMap.put("#2主变500kV", "2号主变500KV套管升高座");

        // 给模型文件赋值
        AVAILABLE_MODELS_MAP.put("LSTM", modelFilePath + File.separator + "LSTM.h5");
        AVAILABLE_MODELS_MAP.put("GRU", modelFilePath + File.separator + "GRU.h5");
    }

    @PostConstruct
    public void load() {
        List<Equipment> equipmentList = equipmentDao.selectList(null);
        for (Equipment equipment : equipmentList) {
            equipmentMap.put(equipment.getEquipmentName(), equipment.getEquipmentId());
        }
    }

    @Override
    public List getOneFromTemperature(String deviceNum, String deviceName, String date) {
        LambdaQueryWrapper<Temperature> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(Temperature::getDeviceNumber, deviceNum);
        queryWrapper.eq(Temperature::getEquipmentName, deviceName);
        queryWrapper.ge(Temperature::getCheckTime, date + " 00:00:00");
        queryWrapper.le(Temperature::getCheckTime, date + " 23:59:59");
        List<Temperature> temperatureList = temperatureDao.selectList(queryWrapper);

        List timeList = new ArrayList();//时间
        List equipmentTemperatureList = new ArrayList();//设备温度
        List environmentTemperatureList = new ArrayList();//环境温度
        List environmentHumidityList = new ArrayList();//湿度
        List windSpeedList = new ArrayList();//风速
        List windDirectionList = new ArrayList();//风向
        List equipmentNameList = new ArrayList();//设备名字
        List holderList = new ArrayList();//云台方位

        //将查询的结果list的每个属性add到每个属性List中
        for (Temperature temperature : temperatureList) {
            timeList.add(temperature.getCheckTime());
            equipmentTemperatureList.add(temperature.getEquipmentTemperature());
            environmentTemperatureList.add(temperature.getEnvTemperature());
            environmentHumidityList.add(temperature.getHumidity());
            windSpeedList.add(temperature.getWindSpeed());
            windDirectionList.add(temperature.getWindDirection());
            equipmentNameList.add(temperature.getEquipmentName());
            holderList.add(temperature.getPantiltLocation());
        }

        //把每个属性集合，放进一个list中
        List list = new ArrayList();
        list.add(timeList);
        list.add(equipmentTemperatureList);
        list.add(environmentTemperatureList);
        list.add(environmentHumidityList);
        list.add(windSpeedList);
        list.add(windDirectionList);
        list.add(equipmentNameList);
        list.add(holderList);

        //增加一个列*(温度 气压等) Stringbuffer类型的  也添加到list中
        StringBuffer titleSB = new StringBuffer("温度 气压等");
        list.add(titleSB);

        return list;
    }

    @Override
    public List getDeviceNumbers() {
        //工具类CollUtil，判断集合是否为空
        if (CollUtil.isNotEmpty(deviceNumberlList)) {
            return deviceNumberlList;
        }

        List<String> deviceNumbers = temperatureDao.getDeviceNumbers();
        for (String deviceNumber : deviceNumbers) {
            Map<String, String> model = new HashMap<>();
            model.put("label", deviceNumber);
            model.put("value", deviceNumber);
            deviceNumberlList.add(model);
        }
        return deviceNumberlList;
    }

    @Override
    public List getDeviceNames() {
        if (CollUtil.isNotEmpty(deviceNamelList)) {
            return deviceNamelList;
        }
        List<String> deviceNames = temperatureDao.getDeviceNames();
        for (String deviceName : deviceNames) {
            Map<String, String> model = new HashMap<>();
            model.put("label", deviceName);
            model.put("value", deviceName);
            deviceNamelList.add(model);
        }
        return deviceNamelList;
    }

    @Override
    public List getAvailableModel() {
        List<Map<String, String>> modelList = new ArrayList<>();
        //AVAILABLE_MODELS_MAP key为模型名字，值为模型地址
        for (String key : AVAILABLE_MODELS_MAP.keySet()) {
            Map<String, String> model = new HashMap<>();
            model.put("label", key + "模型");
            model.put("value", key);
            modelList.add(model);
        }
        return modelList;
    }

    @Override
    public boolean uploadTemperatureExcel(MultipartFile multipartFile, String date) throws Exception {
        if (null == multipartFile || multipartFile.isEmpty()) {
            throw new Exception("上传的文件对象不存在...");
        }

        // 获得完整的文件名
        String fileName = multipartFile.getOriginalFilename();
        System.out.println("fileName = " + fileName);
        // 获得文件后缀名
        String extName = FilenameUtils.getExtension(fileName);
        if (StringUtils.isEmpty(extName)) {
            throw new RuntimeException("文件类型未定义不能上传...");
        }
        if (fileName.length() > 99) {
            fileName = StrUtil.subPre(fileName, 90).concat(".").concat(extName);
        }

        DateTime dateTime = null;

        // 文件名格式和是否上传过校验
        if (StringUtils.isNotEmpty(fileName) && fileName.contains("#")) {
            // 2021/08/01 2021-08-01 2021年8月1日
            String fileDate = fileName.split("#")[0];
            String fileSubName = fileName.split("#")[1];
            if (StringUtils.isNotEmpty(fileDate) && StringUtils.isNotEmpty(fileSubName)) {
                try {
                    dateTime = cn.hutool.core.date.DateUtil.parseDate(fileDate);
                } catch (Exception e) {
                    throw new PowerException("日期格式错误！请上传形如【2021年8月24日#1主变35kV测日报.xlsx】名称的文件");
                }
                // 文件上传之前检查上传记录表是否已经记录过该文件上传记录
                LambdaQueryWrapper<Upload> queryWrapper = new LambdaQueryWrapper<>();
                //判断filename是否存在
                queryWrapper.eq(Upload::getFileName, fileName);
                //判断文件时间是否存在
                queryWrapper.eq(Upload::getFileDate, dateTime.toDateStr());
                //判断文件类型是否存在(temperature)
                queryWrapper.eq(Upload::getFileType, "temperature");
                List uploadList = uploadDao.selectList(queryWrapper);
                if (CollUtil.isNotEmpty(uploadList)) { // 已经上传过
                    throw new Exception("该文件已经上传过，请勿重复上传！请为文件名添加日期以做区分！");
                }
            } else {
                throw new PowerException("文件名格式不符合要求，请上传形如【2021年8月24日#1主变35kV测日报.xlsx】名称的文件");
            }
        } else {
            throw new PowerException("文件名格式不符合要求，请上传形如【2021年8月24日#1主变35kV测日报.xlsx】名称的文件");
        }

        // 默认是linux系统
        String rootPath = powerConfig.getUpload_file_path().replace(" ", "").replace("//", "/");
        if (OS.indexOf("windows") >= 0) {
            rootPath = Constants.UPLOAD_ROOT_PATH_CONFIG_WINDOWS.replace(" ", "").replace("//", "/");
        }



        String extStr = "xlsx";
        int size = 100;
        String type = "file/";


        UploadCommonVo uploadCommonVo = new UploadCommonVo();
        //服务器地址
        uploadCommonVo.setRootPath(rootPath);
        //文件扩展名
        uploadCommonVo.setExtStr(extStr);
        //文件大小
        uploadCommonVo.setSize(size);
        //文件类型
        uploadCommonVo.setType(type);

        //文件大小验证
        // 文件分隔符转化为当前系统的格式
        //fileSize单位MB
        float fileSize = (float) multipartFile.getSize() / 1024 / 1024;
        String fs = String.format("%.2f", fileSize);
        if (fileSize > uploadCommonVo.getSize()) {
            throw new Exception("最大允许上传" + uploadCommonVo.getSize() + " MB的文件, 当前文件大小为 " + fs + " MB");
        }

        // 判断文件的后缀名是否符合规则
        // isContains(extName);
        if (StringUtils.isNotEmpty(uploadCommonVo.getExtStr())) {
            // 切割文件扩展名
            List<String> extensionList = StringUtil.stringToArrayStr(uploadCommonVo.getExtStr());

            if (extensionList.size() > 0) {
                //判断
                if (!extensionList.contains(extName)) {
                    throw new Exception("上传文件的类型只能是：" + uploadCommonVo.getExtStr());
                }
            } else {
                throw new Exception("上传文件的类型只能是：" + uploadCommonVo.getExtStr());
            }
        }

        // 文件名和选择的设备文件是否相同
        // 保存到服务器上
        //文件名
        String newFileName = StringUtil.fileName(extName);
        // 创建目标文件的名称，规则请看destPath方法
        // 规则：  子目录/年/月/日.后缀名
        // 文件分隔符转化为当前系统的格式
        // 文件分隔符转化为当前系统的格式
        String webPath = "/" + DateUtil.nowDate(Constants.DATE_FORMAT_DATE).replace("-", "/") + "/";
        String destPath = FilenameUtils.separatorsToSystem(uploadCommonVo.getRootPath() + webPath) + newFileName;
        // 创建文件
        LOGGER.info(destPath);
        File file = FileUtil.createFile(destPath);

        // 保存文件
        multipartFile.transferTo(file);

        // 判断是否是温度excel表
        if (!ExcelUtils.isTemperatureXlsx(file)) {
            FileUtil.deleteFile(file);
            throw new PowerException("该表格非温度表，请重新选择文件");
        }

        // excel表导入温度表
        List<Map<String, String>> temperatureRows = ExcelUtils.getRowFromExcel(file, "temperature");
        for (Map<String, String> temperatureMap : temperatureRows) {
            try {
                Temperature temperature = JSON.parseObject(JSON.toJSONString(temperatureMap), Temperature.class);
                String equipmentName = temperature.getEquipmentName();
                equipmentName = equipmentName.substring(equipmentName.indexOf(" ") + 1);
                // 如果现有表没有，需要加入到设备表
                if (null == equipmentMap.get(equipmentName)) {
                    Equipment equipment = new Equipment();
                    equipment.setEquipmentName(equipmentName);
                    equipmentDao.insert(equipment);
                    equipmentMap.put(equipmentName, equipment.getEquipmentId());
                }
                temperature.setEquipmentName(equipmentName);
                temperature.setEquipmentId(equipmentMap.get(equipmentName));

                // 去重
                LambdaQueryWrapper<Temperature> queryWrapper1 = new LambdaQueryWrapper<>();
                queryWrapper1.eq(Temperature::getEquipmentName, equipmentName)
                        .eq(Temperature::getCheckTime, temperature.getCheckTime());
                List<Temperature> temperatureList = temperatureDao.selectList(queryWrapper1);
                if (CollUtil.isNotEmpty(temperatureList)) { // 如果已经有该值则跳过不插入
                    LOGGER.warn("重复数据行，跳过");
                    continue;
                } else {
                    temperatureDao.insert(temperature);
                }
            } catch (Exception e) {
                LOGGER.error("解析当前数据行时发生错误，跳过！errMsg：{}", e.getMessage());
            }
        }
         // 没有上传过
        // 将上传记录写入上传记录表
        Upload upload = new Upload();
        upload.setFileName(fileName);
        upload.setFileDate(dateTime.toDateStr());
        upload.setUploadDate(DateUtil.nowDate(Constants.DATE_FORMAT_DATE));
        upload.setFileType("temperature");
        uploadDao.insert(upload);


        System.out.println("-------------------"+file.getPath());
        return true;
    }



    @Override
    public boolean uploadCurrentExcel(MultipartFile multipartFile, String date) throws Exception {
        if (null == multipartFile || multipartFile.isEmpty()) {
            throw new Exception("上传的文件对象不存在...");
        }

        // 文件名
        String fileName = multipartFile.getOriginalFilename();
        System.out.println("fileName = " + fileName);
        // 文件后缀名
        String extName = FilenameUtils.getExtension(fileName);
        if (StringUtils.isEmpty(extName)) {
            throw new RuntimeException("文件类型未定义不能上传...");
        }

        if (fileName.length() > 99) {
            fileName = StrUtil.subPre(fileName, 90).concat(".").concat(extName);
        }

        DateTime dateTime = null;
        // 文件名格式和是否上传过校验
        if (StringUtils.isNotEmpty(fileName) && fileName.contains("#")) {
            String fileDate = fileName.split("#")[0];
            String fileSubName = fileName.split("#")[1];
            if (StringUtils.isNotEmpty(fileDate) && StringUtils.isNotEmpty(fileSubName)) {
                try {
                    dateTime = cn.hutool.core.date.DateUtil.parseDate(fileDate);
                } catch (Exception e) {
                    throw new PowerException("日期格式错误！请上传形如【2021年8月24日#1主变35kV测日报.xlsx】名称的文件");
                }
                // 文件上传之前检查上传记录表是否已经记录过该文件上传记录
                LambdaQueryWrapper<Upload> queryWrapper = new LambdaQueryWrapper<>();
                queryWrapper.eq(Upload::getFileName, fileName);
                queryWrapper.eq(Upload::getFileDate, dateTime.toDateStr());
                queryWrapper.eq(Upload::getFileType, "current");
                List uploadList = uploadDao.selectList(queryWrapper);
                if (CollUtil.isNotEmpty(uploadList)) { // 该文件已经上传过了
                    throw new Exception("该文件已经上传过，请勿重复上传！请为文件名添加日期以做区分！");
                }
            } else {
                throw new PowerException("文件名格式不符合要求，请上传形如【2021年8月24日#1主变35kV测日报.xlsx】名称的文件");
            }
        } else {
            throw new PowerException("文件名格式不符合要求，请上传形如【2021年8月24日#1主变35kV测日报.xlsx】名称的文件");
        }

        // 默认是linux系统
        String rootPath = powerConfig.getUpload_file_path().replace(" ", "").replace("//", "/");
        if (OS.indexOf("windows") >= 0) {
            rootPath = Constants.UPLOAD_ROOT_PATH_CONFIG_WINDOWS.replace(" ", "").replace("//", "/");
        }
        String extStr = "xlsx";
        int size = 100;
        String type = "file/";

        UploadCommonVo uploadCommonVo = new UploadCommonVo();
        uploadCommonVo.setRootPath(rootPath);
        uploadCommonVo.setExtStr(extStr);
        uploadCommonVo.setSize(size);
        uploadCommonVo.setType(type);

        //文件大小验证
        // 文件分隔符转化为当前系统的格式
        float fileSize = (float) multipartFile.getSize() / 1024 / 1024;
        String fs = String.format("%.2f", fileSize);
        if (fileSize > uploadCommonVo.getSize()) {
            throw new Exception("最大允许上传" + uploadCommonVo.getSize() + " MB的文件, 当前文件大小为 " + fs + " MB");
        }

        // 判断文件的后缀名是否符合规则
        // isContains(extName);
        if (StringUtils.isNotEmpty(uploadCommonVo.getExtStr())) {
            // 切割文件扩展名
            List<String> extensionList = StringUtil.stringToArrayStr(uploadCommonVo.getExtStr());

            if (extensionList.size() > 0) {
                //判断
                if (!extensionList.contains(extName)) {
                    throw new Exception("上传文件的类型只能是：" + uploadCommonVo.getExtStr());
                }
            } else {
                throw new Exception("上传文件的类型只能是：" + uploadCommonVo.getExtStr());
            }
        }

        // 文件名和选择的设备文件是否相同
        // 保存到服务器上
        //文件名
        String newFileName = StringUtil.fileName(extName);
        // 创建目标文件的名称，规则请看destPath方法
        // 规则：  子目录/年/月/日.后缀名
        // 文件分隔符转化为当前系统的格式
        // 文件分隔符转化为当前系统的格式
        String webPath = "/" + DateUtil.nowDate(Constants.DATE_FORMAT_DATE).replace("-", "/") + "/";
        String destPath = FilenameUtils.separatorsToSystem(uploadCommonVo.getRootPath() + webPath) + newFileName;
        System.out.println("webpath"+webPath);
        System.out.println("*******************");
        System.out.println("destpath"+destPath);
        // 创建文件
        File file = FileUtil.createFile(destPath);

        // 保存文件
        multipartFile.transferTo(file);

        // 判断是否是电流excel表
        if (!ExcelUtils.isCurrentXlsx(file)) {
            FileUtil.deleteFile(file);
            throw new PowerException("该表格非电流表，请重新选择文件");
        }

        String realName = fileName.substring(fileName.indexOf("#"), fileName.indexOf("测"));
        String equipmentNamePrefix = currentMap.get(realName);
        // excel表导入温度表
        List<Map<String, String>> currentRows = ExcelUtils.getRowFromExcel(file, "current");
        for (Map<String, String> currentMap : currentRows) {
            if (currentMap.size() == 0) {
                break;
            }
            Current currentA = new Current();
            currentA.setEquipmentName(equipmentNamePrefix + "A相");
            currentA.setEquipmentId(equipmentMap.get(equipmentNamePrefix + "A相"));
            currentA.setCurrent(currentMap.get("A相"));
            currentA.setTime(dateTime.toDateStr());

            Current currentB = new Current();
            currentB.setEquipmentName(equipmentNamePrefix + "B相");
            currentB.setEquipmentId(equipmentMap.get(equipmentNamePrefix + "B相"));
            currentB.setCurrent(currentMap.get("B相"));
            currentB.setTime(dateTime.toDateStr());

            Current currentC = new Current();
            currentC.setEquipmentName(equipmentNamePrefix + "C相");
            currentC.setEquipmentId(equipmentMap.get(equipmentNamePrefix + "C相"));
            currentC.setCurrent(currentMap.get("C相"));
            currentC.setTime(dateTime.toDateStr());

            currentDao.insert(currentA);
            currentDao.insert(currentB);
            currentDao.insert(currentC);
        }
        // 没有上传过，因此现在上传
        // 将上传记录写入上传记录表
        Upload upload = new Upload();
        upload.setFileName(fileName);
        upload.setFileDate(dateTime.toDateStr());
        upload.setUploadDate(DateUtil.nowDate(Constants.DATE_FORMAT_DATE));
        upload.setFileType("current");
        uploadDao.insert(upload);
        return true;
    }

    @Override
    public PageInfo<Equipment> listEquipment(PageParamRequest pageParamRequest) {
        Page<Object> startPage = PageHelper.startPage(pageParamRequest.getPage(), pageParamRequest.getLimit());
        List equipmentList = equipmentDao.selectList(null);
        if (CollUtil.isEmpty(equipmentList)) {
            return new PageInfo<>();
        }
        return CommonPage.copyPageInfo(startPage, equipmentList);
    }

    @Override
    public boolean addEquipment(String equipmentName) {
        Equipment equipment = new Equipment();
        equipment.setEquipmentName(equipmentName);
        LambdaQueryWrapper<Equipment> queryWrapper = new LambdaQueryWrapper<>();
        //Equipment::getEquipmentName Equipment的getEquipmentName方法
        queryWrapper.eq(Equipment::getEquipmentName, equipmentName);
        List equipmentList = equipmentDao.selectList(queryWrapper);
        // 表中不存在此设备
        if (CollUtil.isEmpty(equipmentList)) {
            if (equipmentDao.insert(equipment) > 0) {
                return true;
            }
        }
        return false;
    }

    @Override
    public boolean mergeFeature() {
        // 清空特征表数据（防止重复数据）
        featureDao.truncateFeature();
        // 将电流表和温度表关联，合并得到特征表
        List<CountResponse> currentResponseList = currentDao.getEquipmentCurrent();
        for (CountResponse response : currentResponseList) {
            // 校验电流表与温度表是否有能够匹配的数据
            LambdaQueryWrapper<Temperature> queryWrapper = new LambdaQueryWrapper<>();
            queryWrapper.eq(Temperature::getEquipmentName, response.getEquipmentName());
            queryWrapper.last(" and DATE_FORMAT(check_time, '%Y-%m-%d') = '" + response.getDate() + "'");
            List<Temperature> temperatureList = temperatureDao.selectList(queryWrapper);
            int temperatureCount = temperatureList.size();
            if (0 == temperatureCount) {
                continue;
            }
            LambdaQueryWrapper<Current> queryWrapper1 = new LambdaQueryWrapper<>();
            queryWrapper1.eq(Current::getEquipmentName, response.getEquipmentName());
            queryWrapper1.last(" and DATE_FORMAT(time, '%Y-%m-%d') = '" + response.getDate() + "'");
            List<Current> currentList = currentDao.selectList(queryWrapper1);
            // 如果温度表数据少于电流表（电流表数据多） - 将电流表数据只保留部分数据插入
            // 温度表10条数据，电流表13条数据，只取电流表前10条数据插入到温度表中
            if (response.getNum() >= temperatureCount) {
                for (int i = 0; i < temperatureCount; i++) {
                    Temperature temperature = temperatureList.get(i);
                    temperature.setLoadCurrent(currentList.get(i).getCurrent());

                    insertIntoFeature(temperature);
                }
            } else {
                // 如果温度表数据多于电流表（电流表数据少） - 循环数据插入
                int c_idx = 0;
                for (int i = 0; i < temperatureCount; i++) {
                    c_idx = c_idx % currentList.size();
                    Temperature temperature = temperatureList.get(i);
                    temperature.setLoadCurrent(currentList.get(c_idx).getCurrent());
                    c_idx++;

                    insertIntoFeature(temperature);
                }
            }

            // 把电流表插入温度表中，从电流表中取与温度表时间最接近的两条记录
            // 如果其中最接近的一条在10分钟以内，就用这一条数据填充
            // 如果两个时间都在十分钟开外，则把这两个时间的电流取平均值填充
            /*for (int i = 0; i < temperatureCount; i++) {
                Temperature temperature = temperatureList.get(i);
                // 取最近的两条电流数据
                LambdaQueryWrapper<Current> queryWrapper1 = new LambdaQueryWrapper<>();
                queryWrapper1.eq(Current::getEquipmentName, response.getEquipmentName());
                queryWrapper1.last(" and DATE_FORMAT(time, '%Y-%m-%d') = '" + response.getDate() + "' order by (UNIX_TIMESTAMP(time) - UNIX_TIMESTAMP('" + temperature.getCheckTime() + "')) limit 2");
                List<Current> currentList = currentDao.selectList(queryWrapper1);
                double avg = 0.0;
                for (Current c : currentList) {
                    avg += Double.parseDouble(c.getCurrent()) / currentList.size();
                }
                temperature.setLoadCurrent(String.valueOf(avg));
            }*/
        }
        return true;
    }

    /**
     * 构造完整temperature对象，向特征表插入数据
     *
     * @param temperature
     * @return
     */
    private boolean insertIntoFeature(Temperature temperature) {
        // 构造feature对象，检查是否已经导入过，没有导入过就导入
        LambdaQueryWrapper<Feature> queryWrapper2 = new LambdaQueryWrapper<>();
        queryWrapper2.eq(Feature::getEquipmentName, temperature.getEquipmentName());
        queryWrapper2.eq(Feature::getCheckTime, temperature.getCheckTime());
        List<Feature> featureList = featureDao.selectList(queryWrapper2);
        if (CollUtil.isEmpty(featureList)) { // 没有导入过数据
            Feature feature = new Feature();
            BeanUtils.copyProperties(temperature, feature);
            // 如果数据列有空值，跳过
            if (!feature.checkValid()) {
                LOGGER.warn("feature has empty column, skip it. feature: {}", feature);
                return false;
            }
            return featureDao.insert(feature) > 0;
        } else {
            LOGGER.warn("特征表已经导入过数据");
        }
        return false;
    }

    @Override
    public boolean trainModel(TrainParam trainParam) {

        String generatingModel = trainParam.getGeneratingModel();

        // 调用模型归并，生成最新的数据
        mergeFeature();

        // 判断输入的模型类型是否支持
        if (!AVAILABLE_MODELS_MAP.containsKey(generatingModel)) {
            throw new PowerException("您选择了不支持的模型类型，请重新选择[ LSTM或GRU ]！");
        }
        LOGGER.info("当前选择的模型类型是：{}", generatingModel);

        boolean genRst = generateCvsFileFromDB("train");
        if (!genRst) {
            throw new PowerException("生成训练数据集feature.csv时发生错误！");
        }
        String python = powerConfig.getPython();
        String scriptPath = powerConfig.getModel_script_path() + File.separator + "TrainModel.py";
        if (!FileUtil.exist(scriptPath)) {
            throw new PowerException("在" + powerConfig.getModel_script_path() + "路径下不存在模型训练脚本TrainModel.py！请将脚本上传到该路径下");
        }
        String inputDataSet =  powerConfig.getInput_csv_path() + File.separator + "trainfeature.csv";
        if (!FileUtil.exist(inputDataSet)) {
            throw new PowerException("训练数据集feature.csv不存在");
        }
        String outputModelPath = powerConfig.getModel_file_path() + File.separator + generatingModel + /*"_" + DateUtil.nowDate(Constants.DATE_TIME_FORMAT_NUM) +*/ ".h5";

        // 使用Future调用模型训练
        Future<Boolean> trainFuture = executorService.submit(new Callable<Boolean>() {
            @Override
            public Boolean call() throws Exception {
                return PyCmdUtil.trainModel(python, scriptPath, inputDataSet, generatingModel, outputModelPath,
                        trainParam.getLayerArgv(), trainParam.getDropoutArgv(),
                        trainParam.getDenseArgv(), trainParam.getLossArgv(),
                        trainParam.getMonitorArgv(), trainParam.getPatienceArgv(),
                        trainParam.getFactorArgv(), trainParam.getMin_lrArgv(),
                        trainParam.getBatch_sizeArgv(), trainParam.getEpochsArgv());
            }
        });
        try {
            return trainFuture.get(120, TimeUnit.SECONDS);
        } catch (InterruptedException e) {
            throw new PowerException("调用模型训练脚本时发生错误！errMsg: " + e.getMessage());
        } catch (ExecutionException e) {
            throw new PowerException("调用模型训练脚本时发生错误！errMsg: " + e.getMessage());
        } catch (TimeoutException e) {
            throw new PowerException("调用模型训练脚本时发生错误！errMsg: " + e.getMessage());
        }
    }

    @Override
    public List predictModel(String usingModel) {
        List resultList = new LinkedList<>();
        // 判断输入的模型类型是否支持
        if (!AVAILABLE_MODELS_MAP.containsKey(usingModel)) {
            throw new PowerException("您选择了不支持的模型类型，请重新选择[ LSTM或GRU ]！");
        }
        LOGGER.info("当前选择的模型类型是：{}", usingModel);

        //生成最新的数据库feature表
        mergeFeature();

        boolean genRst = generateCvsFileFromDB("predict");
        if (!genRst) {
            throw new PowerException("生成预测数据集feature.csv时发生错误！");
        }


        String python = powerConfig.getPython();
        String scriptPath = powerConfig.getModel_script_path() + File.separator + "PredictModel.py";
        if (!FileUtil.exist(scriptPath)) {
            throw new PowerException("在" + powerConfig.getModel_script_path() + "路径下不存在模型预测脚本PredictModel.py！请将脚本上传到该路径下");
        }
        String inputDataSet =  powerConfig.getInput_csv_path() + File.separator + "predictfeature.csv";
        if (!FileUtil.exist(inputDataSet)) {
            throw new PowerException("预测数据集feature.csv不存在");
        }

        String usingModelPath = powerConfig.getModel_file_path() + File.separator + usingModel + ".h5";
        String outputDataSet = "";
        // 偏差值路径
        String outputDeviation = "";
        if ("GRU".equals(usingModel)) {
            outputDataSet = powerConfig.getOutput_csv_path() + File.separator + "feature_output_GRU.csv";
            outputDeviation = powerConfig.getOutput_csv_path() + File.separator + "feature_output_deviation_GRU.csv";
        } else if ("LSTM".equals(usingModel)) {
            outputDataSet = powerConfig.getOutput_csv_path() + File.separator + "feature_output_LSTM.csv";
            outputDeviation = powerConfig.getOutput_csv_path() + File.separator + "feature_output_deviation_LSTM.csv";
        }
        try {
            FileUtil.createFile(outputDataSet);
            FileUtil.createFile(outputDeviation);
        } catch (Exception e) {
            throw new PowerException("创建模型预测结果文件失败！" + outputDataSet + ", " + outputDeviation);
        }
        final String finalOutputDataSet = outputDataSet;
        final String finalOutputDeviation = outputDeviation;
        //boolean predictRst =  PyCmdUtil.predictModel(scriptPath, inputDataSet, usingModelPath, outputDataSet);

        // 使用Future调用模型预测
        Future<Boolean> predictFuture = executorService.submit(new Callable<Boolean>() {
            @Override
            public Boolean call() throws Exception {
                return PyCmdUtil.predictModel(python, scriptPath, inputDataSet, usingModelPath, finalOutputDataSet, finalOutputDeviation);
            }
        });
        try {
            boolean rst = predictFuture.get(360, TimeUnit.SECONDS);
            if (rst) {
                List<String> dataList = new LinkedList<>();
                List<String> timeList = new LinkedList<>();
                List<String> deviationList = new LinkedList<>();
                // 修改
                // 真实值
                List<String> realDataList = new LinkedList<>();

                // 加载预测结果集并赋值到结果中
                // dataList
                List<CsvRow> csvRows = CsvUtils.read(finalOutputDataSet);

                //取最后30条数据显式
                for (int i = csvRows.size()-30; i < csvRows.size(); i++) {
                    dataList.add(csvRows.get(i).get(1)); // 只取每行的第一列
                    // 修改
                    realDataList.add(csvRows.get(i).get(7)); // 取第7列
                    timeList.add((csvRows.get(i).get(8)));//取第13列

                }

                //追加预测的5个值
                for(int i = 2; i<7; ++i){
                    dataList.add(csvRows.get(csvRows.size()-1).get(i));
                }
//                for(int i = 0; i<5; ++i){
//                    realDataList.add(csvRows.get(csvRows.size()-1).get(7));
//                }
                // timeList 计算对应每个记录的时间
                String lasTime = predictFeatureDao.getLastTimeInFeature();
                System.out.println(csvRows.size());
                for (int i = 0; i < 5; i++) {
                    String time = DateUtil.dateToStr(DateUtil.addSecond(DateUtil.strToDate(lasTime, Constants.DATE_FORMAT), 60*60), Constants.DATE_FORMAT);
                    timeList.add(time);
                    lasTime = time;
                }
                // deviationList
                List<CsvRow> deviationRows = CsvUtils.read(finalOutputDeviation);
                if (deviationRows.size() > 0) {
                    for (int i = 1; i <= 2 ; i++) {
                        double d = Double.parseDouble(deviationRows.get(1).get(i));
                        deviationList.add(String.format("%.3f", d)); // 只取每行的第一列
                    }
                }

                resultList.add(dataList);
                resultList.add(timeList);
                resultList.add(deviationList);
                // 修改
                resultList.add(realDataList);
                return resultList;
            }
        } catch (InterruptedException e) {
            throw new PowerException("调用模型预测脚本时发生错误！errMsg: " + e.getMessage());
        } catch (ExecutionException e) {
            throw new PowerException("调用模型预测脚本时发生错误！errMsg: " + e.getMessage());
        } catch (TimeoutException e) {
            throw new PowerException("调用模型预测脚本时发生错误！errMsg: " + e.getMessage());
        }
        // 读取预测结果
        return resultList;
    }

    /**
     * 读取mysql数据库feature表，转化为csv文件
     */
    public synchronized boolean generateCvsFileFromDB(String name) {
        // 表头
        // select column_comment from information_schema.COLUMNS where table_name = 'feature' and table_schema = 'power' and column_key != 'PRI';
        List<String> headers = featureDao.getColumnCommnet();
        Feature featureHeader = new Feature();
        featureHeader.setCheckTime(headers.get(0));
        featureHeader.setEquipmentName(headers.get(1));
        featureHeader.setEquipmentTemperature(headers.get(2));
        featureHeader.setIntervalUnit(headers.get(3));
        featureHeader.setPhase(headers.get(4));
        featureHeader.setEnvTemperature(headers.get(5));
        featureHeader.setHumidity(headers.get(6));
        featureHeader.setWindSpeed(headers.get(7));
        featureHeader.setWindDirection(headers.get(8));
        featureHeader.setLoadCurrent(headers.get(9));

        // 数据
        LambdaQueryWrapper<Feature> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.select(Feature::getCheckTime, Feature::getEquipmentName, Feature::getEquipmentTemperature, Feature::getIntervalUnit,
                Feature::getPhase, Feature::getEnvTemperature, Feature::getHumidity, Feature::getWindSpeed, Feature::getWindDirection,
                Feature::getLoadCurrent);
        // queryWrapper.last(" limit 10000");
        List<Feature> featureList = featureDao.selectList(queryWrapper);

        // 把表头数据加入最终的表格
        featureList.add(0, featureHeader);

        // list 转换为 String[]
        List<String[]> featureListStr = new LinkedList<>();
        for (Feature feature: featureList) {
            String[] featuerStr = new String[10];
            featuerStr[0] = feature.getCheckTime();
            featuerStr[1] = feature.getEquipmentName();
            featuerStr[2] = feature.getEquipmentTemperature();
            featuerStr[3] = feature.getIntervalUnit();
            featuerStr[4] = feature.getPhase();
            featuerStr[5] = feature.getEnvTemperature();
            featuerStr[6] = feature.getHumidity();
            featuerStr[7] = feature.getWindSpeed();
            featuerStr[8] = feature.getWindDirection();
            featuerStr[9] = feature.getLoadCurrent();
            featureListStr.add(featuerStr);
        }

        // 调用工具类生成csv文件
        Future<Boolean> createCsvFuture = executorService.submit(new Callable<Boolean>() {
            @Override
            public Boolean call() throws Exception {
                return CsvUtils.createCsvFile(featureListStr, powerConfig.getInput_csv_path(), name + "feature.csv");
            }
        });
        try {
            boolean rst = createCsvFuture.get(30, TimeUnit.SECONDS);
            if (rst) {
                return true;
            }
        } catch (InterruptedException e) {
            throw new PowerException("生成csv文件超时或者失败！errMsg: " + e.getMessage());
        } catch (ExecutionException e) {
            throw new PowerException("生成csv文件超时或者失败！errMsg: " + e.getMessage());
        } catch (TimeoutException e) {
            throw new PowerException("生成csv文件超时或者失败！errMsg: " + e.getMessage());
        }
        return false;
    }

    /*-----------------V2-------------------*/

    @Override
    public Map<String, Integer> getCount() {
        Integer envCount = getEnvCount();
        Integer alarmCount = getAlarmCount();
        Integer currentCount = getCurrentCount();
        Integer featureCount = getFeatureCount();

        Map<String, Integer> countMap = new HashMap<>();
        countMap.put("envCount", envCount);
        countMap.put("alarmCount", alarmCount);
        countMap.put("currentCount", currentCount);
        countMap.put("featureCount", featureCount);
        return countMap;
    }

    @Override
    public List<Temperature> previewEnvironmentTable() {
        LambdaQueryWrapper<Temperature> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.select(Temperature::getCheckTime, Temperature::getEquipmentName, Temperature::getEquipmentTemperature);
        queryWrapper.last(" order by temperature_id desc limit 10");
        List<Temperature> temperatureList = temperatureDao.selectList(queryWrapper);
        return temperatureList;
    }

    @Override
    public List<Current> previewCurrentTable() {
        LambdaQueryWrapper<Current> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.select(Current::getTime, Current::getEquipmentName, Current::getCurrent);
        queryWrapper.last(" order by current_id desc limit 10");
        List<Current> currentList = currentDao.selectList(queryWrapper);
        return currentList;
    }

    @Deprecated
    @Override
    public List<Feature> getTodayTemperature() {
        return null;
    }

    @Override
    public Map<String, String> getPearsonParam() {
        Map<String, String> pearsonMap = new HashMap<>();
        pearsonMap.put("equipmentName", "0.198255235971254");
        pearsonMap.put("equipmentTemperature", "1");
        pearsonMap.put("intervalUnit", "0.20419587748292");
        pearsonMap.put("phase", "0.20337114852446");
        pearsonMap.put("environtmentTemperature", "0.34855100066201");
        pearsonMap.put("humidity", "0.224137114910603");
        pearsonMap.put("windSpeed", "0.224236346546858");
        pearsonMap.put("windDirection", "0.29779912151045");
        pearsonMap.put("loadCurrent", "0.552904406335777");
        return pearsonMap;
    }

    @Override
    public Map<String, Object> getTemperatureTop() {
        List<Map> temperatureTopList = temperatureDao.getTemperatureTop();
        List<Object> xAxisList = new ArrayList<>();
        List<Object> yAxisList = new ArrayList<>();
        for (Map map : temperatureTopList) {
            xAxisList.add(map.get("count"));
            yAxisList.add(map.get("equipment_name"));
        }
        Map<String, Object> topResult = new HashMap<>();
        topResult.put("xAxis", xAxisList);
        topResult.put("yAxis", yAxisList);
        return topResult;
    }

    @Override
    public List getCurrentCompare() {
        List lstmList = new ArrayList();
        List gruList = new ArrayList();
        List timeList = new ArrayList();
        List lstmDeviationList = new ArrayList();
        List gruDeviationList = new ArrayList();

        String outputDataSetLSTM = powerConfig.getOutput_csv_path() + File.separator + "feature_output_LSTM.csv";
        String outputDataSetGRU = powerConfig.getOutput_csv_path() + File.separator + "feature_output_GRU.csv";
        String outputDeviationLSTM = powerConfig.getOutput_csv_path() + File.separator + "feature_output_deviation_LSTM.csv";
        String outputDeviationGRU = powerConfig.getOutput_csv_path() + File.separator + "feature_output_deviation_GRU.csv";
        boolean lstmExist = FileUtil.exist(outputDataSetLSTM) && FileUtil.exist(outputDeviationLSTM);
        boolean gruExist = FileUtil.exist(outputDataSetGRU) && FileUtil.exist(outputDeviationGRU);

        if (!lstmExist) { // 不存在则请求一次
            lstmList = (List) predictModel("LSTM").get(0);
        } else {
            // 加载预测结果集并赋值到结果中
            List<CsvRow> csvRows = CsvUtils.read(outputDataSetLSTM);
            for (int i = 1; i < csvRows.size(); i++) {
                lstmList.add(csvRows.get(i).get(1));
            }
            // 加载误差值结果集deviationList
            List<CsvRow> deviationRows = CsvUtils.read(outputDeviationLSTM);
            /*for (int i = 1; i < deviationRows.size(); i++) {
                lstmDeviationList.add(deviationRows.get(i).get(1)); // 只取每行的第一列
            }*/
            if (deviationRows.size() > 0) {
                for (int i = 1; i <= 2 ; i++) {
                    double d = Double.parseDouble(deviationRows.get(1).get(i));
                    lstmDeviationList.add(String.format("%.3f", d)); // 只取每行的第一列
                }
            }
        }
        if (!gruExist) { // 不存在则请求一次
            gruList = (List) predictModel("GRU").get(0);
        } else {
            List<CsvRow> csvRows = CsvUtils.read(outputDataSetGRU);
            for (int i = 1; i < csvRows.size(); i++) {
                gruList.add(csvRows.get(i).get(1));
            }
            // deviationList
            List<CsvRow> deviationRows = CsvUtils.read(outputDeviationGRU);
            /*for (int i = 1; i < deviationRows.size(); i++) {
                gruDeviationList.add(deviationRows.get(i).get(1)); // 只取每行的第一列
            }*/
            if (deviationRows.size() > 0) {
                for (int i = 1; i <= 2 ; i++) {
                    double d = Double.parseDouble(deviationRows.get(1).get(i));
                    gruDeviationList.add(String.format("%.3f", d)); // 只取每行的第一列
                }
            }
        }

        // 计算对应每个记录的时间
        String lasTime = featureDao.getLastTimeInFeature();
        for (int i = 1; i < Math.min(lstmList.size(), gruList.size()); i++) {
            String time = DateUtil.dateToStr(DateUtil.addSecond(DateUtil.strToDate(lasTime, Constants.DATE_FORMAT), 60), Constants.DATE_FORMAT);
            timeList.add(time);
            lasTime = time;
        }

        List resultList = new ArrayList();
        resultList.add(lstmList);
        resultList.add(gruList);
        resultList.add(timeList);
        resultList.add(lstmDeviationList);
        resultList.add(gruDeviationList);
        return resultList;
    }

    private Integer getEnvCount() {
        Integer envCount = temperatureDao.selectCount(null);
        return envCount;
    }

    private Integer getAlarmCount() {
        LambdaQueryWrapper<Temperature> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.ne(Temperature::getAlarmLevel, "未告警");
        Integer alarmCount = temperatureDao.selectCount(queryWrapper);
        return alarmCount;
    }

    private Integer getCurrentCount() {
        Integer currentCount = currentDao.selectCount(null);
        return currentCount;
    }

    private Integer getFeatureCount() {
        Integer featureCount = featureDao.selectCount(null);
        return featureCount;
    }
}
